﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using RED_NET_Core.Helper;

namespace RED_NET_Core.DAL
{
    public class Manager_DAL
    {
        //构造函数
        public Manager_DAL()
        { }

        //批量操作

        #region[将Excel表导成DataSet]

        /// <summary>
        /// 返回值是dataset以用于管理员导入用户帐号密码
        /// </summary>
        ///  <param name="fileNamePath"></param>//文件在服务器上的路径
        /// <param name="table"></param>//文件名
        /// <returns></returns>
        public DataSet TransformFromExcelToDataSet(string fileNamePath, string table)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source = " + fileNamePath +
                ";Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection cn = new OleDbConnection(strConn);//链接 打开表
            try
            {
                cn.Open();
                OleDbDataAdapter OleAdp = new OleDbDataAdapter("select * from [Sheet1$]", cn);//
                DataSet ds = new DataSet();
                OleAdp.Fill(ds, table);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[利用dataset批量导入用户]

        public int InsetIntoUser(DataSet ds)
        {
            //判断导入的excel表是否有我要的列，没有则返回-1
            if (!ds.Tables[0].Columns.Contains("用户名")) return -1;
            if (!ds.Tables[0].Columns.Contains("学院")) return -1;
            if (!ds.Tables[0].Columns.Contains("专业")) return -1;
            if (!ds.Tables[0].Columns.Contains("班级")) return -1;
            if (!ds.Tables[0].Columns.Contains("昵称")) return -1;
            int sum = 0;//计算注册失败（数据错误，或者用户名已存在）的个数并返回，表示没有再次插入
            SqlHelper helper = new SqlHelper();
            DataRow[] dr = ds.Tables[0].Select();
            string tUserName, tPassword, tNickName;
            int tCollegeId, tMajorId, tClassId;//, tRightId;
            for (int i = 0; i < dr.Length; i++)
            {
                tUserName = dr[i]["用户名"].ToString();
                //若已存在该用户则不继续操作
                if (IsUserNameExist(tUserName) != 0)
                {
                    sum++;
                    continue;
                }

                tPassword = dr[i]["密码"].ToString();
                try
                {
                    tCollegeId = Int32.Parse(dr[i]["学院"].ToString());
                    tMajorId = Int32.Parse(dr[i]["专业"].ToString());
                    tClassId = Int32.Parse(dr[i]["班级"].ToString());
                    //tRightId = Int32.Parse(dr[i]["权限"].ToString());
                    tNickName = dr[i]["昵称"].ToString();
                    Registe(tUserName, tPassword, tCollegeId, tMajorId, tClassId, 0, tNickName);
                }
                catch (Exception ex)
                {
                    sum++;
                    throw ex;
                }
                continue;
            }
            return sum;
        }

        #endregion

        //用户的操作

        #region[检查该用户名是否已存在]

        public int IsUserNameExist(string userName)
        {
            SqlHelper sqlhelper = new SqlHelper();
            string cmd = "select * from _Users where _UserName = @UserName";
            SqlParameter[] pram = new SqlParameter[]
            {
                new SqlParameter("@UserName",userName)
            };
            SqlCommand sqlcmd = sqlhelper.GetCommand(cmd, pram);
            try
            {
                SqlDataReader sdr = sqlcmd.ExecuteReader();
                if (sdr.Read())
                    return 1;
                else return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[用户自己修改密码]

        /// <summary>
        /// 修改自己的密码
        /// </summary>
        /// <param name="newPassword">新密码</param>
        /// <returns></returns>
        public bool UpdateMyPassword(string newPassword)//,string newAvatarUrl)
        {
            SqlHelper helper = new SqlHelper();
            String cmdText = "update _Users(_Password) values(@Password)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Password",newPassword)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            bool IsOk = true;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
                IsOk = false;
            }
            return IsOk;
        }

        #endregion

        #region[用户自己修改头像]

        /// <summary>
        /// 修改自己的头像
        /// </summary>
        /// <param name="newPassword">新图片地址</param>
        /// <returns></returns>
        public bool UpdateMyAvatar(string avatarUrl)//,string newAvatarUrl)
        {
            SqlHelper helper = new SqlHelper();
            String cmdText = "update _Users(_AvatarUrl) values(@AvatarUrl)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@AvatarUrl",avatarUrl)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            bool IsOk = true;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
                IsOk = false;
            }
            return IsOk;
        }

        #endregion

        #region[返回密码]

        public string GetPassword(int tUserId)
        {
            SqlHelper helper = new SqlHelper();
            String cmdText = "select _Password from _Users　where _= @UserId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserId",tUserId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            string password = null;
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                password = reader["_Password"].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return password;
        }

        #endregion

        //管理员动态图管理

        #region[修改对应用户的动态图对应图片地址]
        /// <summary>
        /// 修改动态图
        /// </summary>
        /// <param name="tUid">对应管理员Id</param>
        /// <param name="tPid">第几张图片</param>
        /// <param name="tNewUrl">新图片地址</param>
        /// <returns></returns>
        public bool UpdateTrendImage(int tUid, int tPid, string tNewUrl ,int tNewsId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "update _TrendImage set _ImageUrl = @ImageUrl,_OfNewsId = @OfNewsId where _ManagerId=@ManagerId and _ImageNo=@ImageNo";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ImageUrl",tNewUrl),
                new SqlParameter("@ManagerId",tUid),
                new SqlParameter("@ImageNo",tPid),
                new SqlParameter("@OfNewsId",tNewsId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            bool x = true;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                x = false;
                throw ex;
            }
            return x;
        }
        #endregion

        #region[对应管理员的四张动态图地址]
        /// <summary>
        /// 获取对应管理员的四张动态图地址
        /// </summary>
        /// <param name="tUid">管理员ID</param>
        /// <returns>返回存储图片的数组ImageUrl[4]</returns>
        public string[] GetTrendOfManager(int tUid)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "select _ImageUrl from _TrendImage where _ManagerId=@ManagerId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ManagerId",tUid)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            String[] ImageUrl = new String[4];
            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                int i = 0;
                while (dr.Read())
                {
                    ImageUrl[i++] = dr["_ImageUrl"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
                return null;
            }
            return ImageUrl;
        }
        #endregion

        #region[对应管理员的四张动态图链接的日志ID]
        /// <summary>
        /// 获取对应管理员的四张动态图地址
        /// </summary>
        /// <param name="tUid">管理员ID</param>
        /// <returns>返回存储图片的数组ImageUrl[4]</returns>
        public string[] GetNewsIdOfTrendImage(int tUid)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "select _OfNewsId from _TrendImage where _ManagerId=@ManagerId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ManagerId",tUid)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            String[] ImageUrl = new String[4];
            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                int i = 0;
                while (dr.Read())
                {
                    ImageUrl[i++] = dr["_OfNewsId"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
                return null;
            }
            return ImageUrl;
        }
        #endregion

        #region[对应管理员的四张动态图链接的日志名字]
        /// <summary>
        /// 获取对应管理员的四张动态图对应的日志名
        /// </summary>
        /// <param name="tUid">管理员ID</param>
        /// <returns>返回存储图片的数组ImageUrl[4]</returns>
        public string[] GetNewsNameOfTrendImage(int tUid)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "select _Title from _News where _Id in (select _OfNewsId from _TrendImage where _ManagerId=@ManagerId)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ManagerId",tUid)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            String[] ImageNewsName = new String[4];
            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                int i = 0;
                while (dr.Read())
                {
                    ImageNewsName[i++] = dr["_Title"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
                return null;
            }
            return ImageNewsName;
        }
        #endregion

        #region[新注册的管理员插入四张图片]
        /// <summary>
        /// 新注册的管理员需要为其插入四张图片
        /// </summary>
        /// <param name="tUid">管理员id</param>
        /// <param name="tImageUrl">默认图片地址</param>
        /// <returns></returns>
        public bool InsertTrendImage(int tUid,String tImageUrl)
        {
            SqlHelper helper = new SqlHelper();
            String cmdText = "insert into _TrendImage(_ImageUrl,_ManagerId,_ImageNo) values('" + tImageUrl.ToString() +
                "','" + tUid.ToString() + "','";
            SqlCommand cmd;
            for(int i=0;i<4;i++)
            {
                cmd = helper.GetCommand(cmdText + i.ToString() + "')",null);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    throw ex;
                    return false;
                }
            }
            return true;
        }
        #endregion

        //学院表的增删改查（超级管理员）

        #region[新建学院]

        //解决插入后立刻返回学院Id
        public int InsertNewCollege(string _CollegeName)
        {
            SqlHelper heleper = new SqlHelper();
            string cmdText = "INSERT INTO _College(_CollegeName) VALUES(@CollegeName) SELECT @@identity as _Id ";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@CollegeName",_CollegeName)
            };
            SqlCommand cmd = heleper.GetCommand(cmdText, param);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    int id = Int32.Parse(sdr["_Id"].ToString());
                    sdr.Dispose();
                    return id;
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[更改学院名]

        public bool UpdateCollegeName(string _CollegeName, int _Id)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "UPDATE _College SET _CollegeName = @CollegeName WHERE _Id = @Id";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@CollegeName",_CollegeName),
                new SqlParameter("@Id",_Id)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[删除学院]

        public bool DeleteCollege(int _Id)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "DELETE _College WHERE _Id = @Id";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Id",_Id)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找学院]

        public DataTable GetColleges()
        {
            SqlHelper helper = new SqlHelper();
            String cmdStr = "SELECT * FROM _College";
            SqlCommand sqlcmd = helper.GetCommand(cmdStr, null);
            DataTable dt;
            try
            {
                SqlDataReader dr = sqlcmd.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        //专业的增删改查（超级管理员）

        #region[新建专业]

        public bool InserNewMajor(string _MajorName, int _CollegeId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "INSERT INTO _Major(_MajorName,_College) VALUES (@MajorName,@CollegeId)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@MajorName",_MajorName),
                new SqlParameter("@CollegeId",_CollegeId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[更新专业]

        public bool UpdateMajor(int _MajorId, string _MajorName)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "UPDATE _Major SET _MajorName = @MajorName WHERE _MajorId = @MajorId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@MajorName",_MajorName),
                new SqlParameter("@MajorId",_MajorId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[删除专业]

        public bool DeleteMajor(int _MajorId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "DELETE _Major WHERE _MajorId = @Id";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Id",_MajorId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找专业]

        public DataTable GetMajorsByCollege(int _CollegeId)
        {
            SqlHelper helper = new SqlHelper();
            String cmdStr = "select * from _Major where _College = @College";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@College",_CollegeId)
            };
            SqlCommand sqlcmd = helper.GetCommand(cmdStr, param);
            DataTable dt;
            try
            {
                SqlDataReader dr = sqlcmd.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        //班级的增删改查（管理员）

        #region[新建班级]

        public int InserNewClass(string _ClassName,int _MajorId, int _CollegeId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "INSERT INTO _Class(_ClassName,_MajorId,_CollegeId) VALUES (@ClassName,@MajorId,@CollegeId) SELECT @@identity as _ClassId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ClassName",_ClassName),
                new SqlParameter("@MajorId",_MajorId),
                new SqlParameter("@CollegeId",_CollegeId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    int classId = Int32.Parse(sdr["_ClassId"].ToString());
                    sdr.Dispose();
                    return classId;
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[新建班级后插入ClassUserId]

        public bool Update_InserNewClass_ClassUserId(int tClassUserId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Update _Class Set _ClassUserId = @ClassUserId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ClassUserId",tClassUserId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[更新班级]

        public bool UpdateClass(int _ClassId, string _ClassName)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "UPDATE _Class SET _ClassName = @ClassName WHERE _ClassId = @ClassId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ClassName",_ClassName),
                new SqlParameter("@ClassId",_ClassId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[删除班级]

        public bool DeleteClass(int _ClassId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "DELETE _Class WHERE _ClassId = @Id";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Id",_ClassId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找班级(学院查找）]

        public DataTable GetClassesByCollege(int _CollegeId)
        {
            SqlHelper helper = new SqlHelper();
            String cmdStr = "select * from _Class where _CollegeId = @College";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@College",_CollegeId)
            };
            SqlCommand sqlcmd = helper.GetCommand(cmdStr, param);
            DataTable dt;
            try
            {
                SqlDataReader dr = sqlcmd.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找班级(专业查找）]

        public DataTable GetClassesByMajor(int _MajorId)
        {
            SqlHelper helper = new SqlHelper();
            String cmdStr = "select * from _Class where _MajorId = @MajorId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@MajorId",_MajorId)
            };
            SqlCommand sqlcmd = helper.GetCommand(cmdStr, param);
            DataTable dt;
            try
            {
                SqlDataReader dr = sqlcmd.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        //用户的增删改查（超级管理员）

        #region[注册用户]

        public int Registe(string _UserName, string _Password, int _College, int _Major, int _Class,
            int _Right, string _NickName)
        {
            SqlHelper helper = new SqlHelper();
            string CmdText = "INSERT INTO _Users(_UserName,_Password,_LaseLoginTime,_Status,_College,_Major,_Class," +
                 "_Right,_MyAttention,_AttentionBy,_BlogNum,_NickName,_AvatarUrl,_VisitId1,_VisitId2,_VisitId3,_VisitId4,_VisitId5," +
                 "_VisitTime1,_VisitTime2,_VisitTime3,_VisitTime4,_VisitTime5,_Top)" +
                 "VALUES(@UserName,@Password,@LaseLoginTime,@Status,@College,@Major,@Class,@Right,@MyAttention,@AttentionBy,@BlogNum" +
                 ",@NickName,@AvatarUrl,@VisitId1,@VisitId2,@VisitId3,@VisitId4,@VisitId5,@VisitTime1,@VisitTime2,@VisitTime3,@VisitTime4,@VisitTime5,@Top)" +
                 "SELECT @@identity as _UId";
            SqlParameter[] param;
            string _AvatarUrl = "../Image_Css/avatar/moren.jpg";
            DateTime time = DateTime.Now;
            if (_Right == 0)//注册团支书账号
            {
                param = new SqlParameter[]
                {
                new SqlParameter("@UserName",_UserName),
                new SqlParameter("@Password",_Password),
                new SqlParameter("@LaseLoginTime",time),
                new SqlParameter("@Status",true),
                new SqlParameter("@College",_College),
                new SqlParameter("@Major",_Major),
                new SqlParameter("@Class",_Class),
                new SqlParameter("@Right",_Right),
                new SqlParameter("@MyAttention","0"),
                new SqlParameter("@AttentionBy","0"),
                new SqlParameter("@BlogNum","0"),
                new SqlParameter("@NickName",_NickName),
                new SqlParameter("@AvatarUrl",_AvatarUrl),
                new SqlParameter("@VisitId1","0"),
                new SqlParameter("@VisitId2","0"),
                new SqlParameter("@VisitId3","0"),
                new SqlParameter("@VisitId4","0"),
                new SqlParameter("@VisitId5","0"),
                new SqlParameter("@VisitTime1",time),
                new SqlParameter("@VisitTime2",time),
                new SqlParameter("@VisitTime3",time),
                new SqlParameter("@VisitTime4",time),
                new SqlParameter("@VisitTime5",time),
                new SqlParameter("@Top","0")
                };
            }
            else if (_Right == 1)//注册学院账号
            {
                param = new SqlParameter[]
                {
                new SqlParameter("@UserName",_UserName),
                new SqlParameter("@Password",_Password),
                new SqlParameter("@LaseLoginTime",time),
                new SqlParameter("@Status",true),
                new SqlParameter("@College",_College),
                new SqlParameter("@Major","-1"),
                new SqlParameter("@Class","-1"),
                new SqlParameter("@Right",_Right),
                new SqlParameter("@MyAttention","0"),
                new SqlParameter("@AttentionBy","0"),
                new SqlParameter("@BlogNum","0"),
                new SqlParameter("@NickName",_NickName),
                new SqlParameter("@AvatarUrl",_AvatarUrl),
                new SqlParameter("@VisitId1","0"),
                new SqlParameter("@VisitId2","0"),
                new SqlParameter("@VisitId3","0"),
                new SqlParameter("@VisitId4","0"),
                new SqlParameter("@VisitId5","0"),
                new SqlParameter("@VisitTime1",time),
                new SqlParameter("@VisitTime2",time),
                new SqlParameter("@VisitTime3",time),
                new SqlParameter("@VisitTime4",time),
                new SqlParameter("@VisitTime5",time),
                new SqlParameter("@Top","0"),
                };
            }
            else
            {
                return -1;
            }
           /* else//注册学校账号
            {
                param = new SqlParameter[]
                {
                new SqlParameter("@UserName",_UserName),
                new SqlParameter("@Password",_Password),
                new SqlParameter("@College",-1),
                new SqlParameter("@Major",-1),
                new SqlParameter("@Class",-1),
                new SqlParameter("@Right",_Right),
                new SqlParameter("@NickName",_NickName),
                new SqlParameter("@AvatarUrl",_AvatarUrl)
                };
            }
            * */
            SqlCommand cmd = helper.GetCommand(CmdText, param);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    int classId = Int32.Parse(sdr["_UId"].ToString());
                    sdr.Dispose();
                    return classId;
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[删除用户]

        public bool DeleteUser(int _UId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "DELETE _Users WHERE  _UId = @UId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UId",_UId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[更新用户]

        //学院专业班级这些  不能更改。
        public bool UpdateUser(int _UId, string _Password,  string _NickName)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "UPDATE _Users SET _Password=@Password,_NickName=@NickName WHERE _UId=@UId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UId",_UId),
                new SqlParameter("@Password",_Password),
                new SqlParameter("@NickName",_NickName)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找学院用户]

        public DataTable SelectCollegeUsers()
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "SELECT _UId,_UserName,_Password,_College,_NickName FROM _Users WHERE _Right = 1";
            SqlCommand cmd = helper.GetCommand(cmdText, null);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                DataTable dt = new DataTable() ;
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        //团支书的查找（超级管理员、管理员）

        #region[ 查找团支书用户(全学院) ]

        public DataTable SelectClassUsersByCollege(int _College)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "SELECT _UId,_UserName,_Password,_College,_Major,_Class,_NickName FROM _Users WHERE _Right = 0 AND _College=@College";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@College",_College)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[ 查找团支书用户(全专业) ]

        public DataTable SelectClassUsersByMajor(int _Major)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "SELECT _UId,_UserName,_Password,_College,_Major,_Class,_NickName FROM _Users WHERE _Right = 0 AND _Major=@Major";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Major",_Major)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

    }

}